﻿--提取岗位异动二级审批列表
CREATE PROCEDURE [dbo].[proc_Employee_PostMove_GetApplySList]
(
	@StartIndex nvarchar(20),
	@EndIndex nvarchar(20),
	@CompanyId nvarchar(400),
	@ename nvarchar(50),
	@audit nvarchar(4)
)
AS
DECLARE @sql NVARCHAR(4000)
DECLARE @swhere NVARCHAR(4000)
SET @swhere=' FirstAuditPerson <> '''' AND FirstAudit=1 and E.Ename like ''%'+@ename+'%'' and ee.Proposer is not null'
SET @swhere=@swhere+' and (case when ee.Secondaudit=1 then 1 when ee.Secondaudit=0 and ee.Secondauditperson is null then 0'
SET @swhere=@swhere+' when ee.Secondaudit=0 and ee.Secondauditperson is not null then 2 end) like ''%'+@audit+'%'''	   
IF(@CompanyId<>'0')
SET @swhere=@swhere+' and EE.BCompanyid in('+@CompanyId+')'
SET @sql=
		+' ;WITH list As(Select ROW_NUMBER() OVER (Order By Secondaudit,SecondauditTime DESC) AS Row,'
		+' EE.Id,'
		+' (select ENumber from Employee E where EE.EId=E.Id) as ENumber,'
		+' (select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,'
		+' EE.EId,'
		+' (select EName from Employee E where EE.EId=E.Id) as EName,'
		+' (select Sex from Employee E where EE.EId=E.Id) as Sex,'
		+' (case Acompanyname when '''' then (select CompanyName from Company where id=Acompanyid) else Acompanyname end) as CompanyName,'
		+' (case Bdepartmentname when '''' then (select DName from Department where id=Bdepartmentid) else Bdepartmentname end) as DName,'
		+' (case APostname when '''' then (select PostName from Post P where id=APostId) else APostname end) as PostName,'
		+' (case BPostName when '''' then (select PostName from Post P where id=BPostId) else BPostName end) as BPostName,'
		+' (case AGradename when '''' then (select Title from Grade G where id=ee.AGradeId) else AGradename end) as GradeName,'
		+' (case BGradeName when '''' then (select Title from Grade G where id=ee.BGradeId) else BGradeName end) as BGradeName,'
		+' EE.FirstAudit,EE.FirstAuditPerson,'
		+' (select EName from Employee E where EE.FirstAuditPerson=E.UserName) as FirstAuditPersonName,'
		+' EE.FirstAuditTime,EE.SecondAudit,EE.SecondAuditPerson,'
		+' (select EName from Employee E where EE.SecondAuditPerson=E.UserName) as SecondAuditPersonName,'
		+' EE.SecondAuditTime,'
		+' IsNull(EE.ExecDate,''1900-01-01'') as ExecDate,'
		+' IsNull(EE.IsExec,0) as IsExec, E.GID'
		+' From employee_PostMove EE Inner Join Employee E on EE.EId=E.Id Where '
		+@swhere
		+' )'
		+' Select *,(SELECT COUNT(0) FROM list) AS coun From list Where Row between '+ @StartIndex +' and '+ @EndIndex + ' Order By Row'
		
		EXEC(@sql)
--print(@sql)


----提取岗位异动二级审批列表
--ALTER proc [dbo].[proc_Employee_PostMove_GetApplySList]
--(
--	@StartIndex int,
--	@EndIndex int,
--	@CompanyId int,
--	@ename nvarchar(50),
--	@audit nvarchar(4)
--)
--as
--Begin

--	;WITH list As(Select ROW_NUMBER() OVER (Order By EE.Id ASC)AS Row,
--		EE.Id,
--		(select ENumber from Employee E where EE.EId=E.Id) as ENumber,
--		(select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,
--		EE.EId,
--		(select EName from Employee E where EE.EId=E.Id) as EName,
--		(select Sex from Employee E where EE.EId=E.Id) as Sex,
--		(select CompanyName from Company where id=Acompanyid) as CompanyName,
--		(select DName from Department where id=Bdepartmentid) as DName,
--		(select PostName from Post P where id=APostId) as PostName,
--		(select PostName from Post P where id=BPostId) as BPostName,
--		(select Title from Grade G where id=ee.AGradeId) as GradeName,
--		EE.FirstAudit,
--		EE.FirstAuditPerson,
--		(select EName from Employee E where EE.FirstAuditPerson=E.UserName) as FirstAuditPersonName,
--		EE.FirstAuditTime,
--		EE.SecondAudit,
--		EE.SecondAuditPerson,
--		(select EName from Employee E where EE.SecondAuditPerson=E.UserName) as SecondAuditPersonName,
--		EE.SecondAuditTime,
--		IsNull(EE.ExecDate,'1900-01-01') as ExecDate,
--		IsNull(EE.IsExec,0) as IsExec
--	From employee_PostMove EE Inner Join Employee E on EE.EId=E.Id
--	Where
--			FirstAuditPerson <> '' AND FirstAudit=1
--		And	(@CompanyId=0 Or EE.BCompanyid = @CompanyId)
--		And E.Ename like '%'+@ename+'%'
--		And ee.Proposer is not null
--		And (case when ee.Secondaudit=1 then '1' 
--				   when ee.Secondaudit=0 and ee.Secondauditperson is null then '0'
--				   when ee.Secondaudit=0 and ee.Secondauditperson is not null then '1' end) like '%'+@audit+'%'
--		--And  e.state <> 5

--	)
--		Select *,(SELECT COUNT(0) FROM list) AS coun From list Where Row between @StartIndex and @EndIndex
--End

RETURN
